package com.lizhen.core.function.dbutils.dao;

import com.lizhen.core.function.dbutils.constant.IJDBC;
import com.lizhen.core.function.dbutils.entity.Person;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * @author lizhen
 */
public class DAOPerson {
    static Connection conn = null;
    static QueryRunner queryRunner = new QueryRunner();
    static ResultSetHandler<Person> resultHandler = new BeanHandler<Person>(Person.class);

    /**
     * @author lizhen
     */
    public DAOPerson() {
    }

    private static Connection createConnection(String driver, String url, String user, String pswd) throws SQLException {
        DbUtils.loadDriver(driver);
        System.out.println("Connecting to database...");
        conn = DriverManager.getConnection(url, user, pswd);
        return conn;
    }

    private static void closeConnection(Connection conn) throws SQLException {
        DbUtils.close(conn);
    }

    public static void insert(String sql, Object... params) {
        try {
            conn = createConnection(IJDBC.MySQL_DRIVER, IJDBC.MySQL_URL, IJDBC.MySQL_USER, IJDBC.MySQL_PSWD);
            int insertedRecords = queryRunner.update(conn, sql, params);

            System.out.println(insertedRecords + " record(s) inserted");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void query(String sql, Object... params) {
        try {
            conn = createConnection(IJDBC.MySQL_DRIVER, IJDBC.MySQL_URL, IJDBC.MySQL_USER, IJDBC.MySQL_PSWD);
            Person person = queryRunner.query(conn, sql, resultHandler, params);

            System.out.print("Name: " + person.getName());
            System.out.println(", Age: " + person.getAge());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void update(String sql, Object... params) {
        try {
            conn = createConnection(IJDBC.MySQL_DRIVER, IJDBC.MySQL_URL, IJDBC.MySQL_USER, IJDBC.MySQL_PSWD);
            int updatedRecords = queryRunner.update(conn, sql, params);

            System.out.println(updatedRecords + " record(s) updated");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void delete(String sql, Object... params) {
        try {
            conn = createConnection(IJDBC.MySQL_DRIVER, IJDBC.MySQL_URL, IJDBC.MySQL_USER, IJDBC.MySQL_PSWD);
            int deletedRecords = queryRunner.update(conn, sql, params);

            System.out.println(deletedRecords + " record(s) deleted");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * @author lizhen
     */
    public static void main(String[] args) {
    }
}
